package service.implement;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Timesheet;
import model.TimesheetDetail;
import service.AbstractConnection;
import service.Constan;
import service.TimesheetAPI;

public class TimesheetDAO extends AbstractConnection implements TimesheetAPI {
	//	private Connection conn=dataConnection();
	@Override
	public int addTimesheet(Timesheet ts) {
		String query="insert into timesheet values(?,?,?,?,?,?,?,?,?)";
		int kq=0;
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1,java.sql.Types.NULL);
			ps.setString(2, ts.getDate());
			ps.setInt(3, ts.getDuration());
			ps.setString(4, ts.getDescription());
			ps.setString(5, ts.getActivity());
			ps.setString(6, ts.getOvertime());
			ps.setString(7, ts.getLocation());
			ps.setInt(8, ts.getEmployeeid());
			ps.setInt(9, ts.getProjectid());
			kq= ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			closeConnection();	
		}
		return kq;
	}
	@Override
	public int updateTimesheet(Timesheet ts) {
		String query = "update timesheet set date=?, duration=?, description=?,activity=?,overtime=?,location=?,employeeid=?,projectid=? where id=?";
		int kq = 0;
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, ts.getDate());
			ps.setInt(2, ts.getDuration());
			ps.setString(3, ts.getDescription());
			ps.setString(4, ts.getActivity());
			ps.setString(5, ts.getOvertime());
			ps.setString(6, ts.getLocation());
			ps.setInt(7, ts.getEmployeeid());
			ps.setInt(8, ts.getProjectid());
			ps.setInt(9, ts.getId());
			kq = ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return kq;
	}
	@Override
	public int deleteTimesheet(int id) {
		String query = "delete from timesheet where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}
	@Override
	public ArrayList<Timesheet> getTimesheet() {
		ArrayList<Timesheet> dsts = new ArrayList<>();
		String query = "SELECT * FROM timesheet";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String date = rs.getString(2);
				int duration = rs.getInt(3);
				String description=rs.getString(4);
				String activity=rs.getString(5);
				String overtime=rs.getString(6);
				String location=rs.getString(7);
				int employeeid=rs.getInt(8);
				int projectid=rs.getInt(9);
				Timesheet ts = new Timesheet(id, date, duration,description,activity,overtime,location,employeeid,projectid);
				dsts.add(ts);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return dsts;
	}

	@Override
	public boolean checkEmployeeid(int id) {
		String query="Select * FROM employee WHERE id=?";
		try{
			PreparedStatement ps=getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				return true;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return false;
	}
	@Override
	public boolean checkProjectid(int id) {
		String query="Select * FROM project WHERE id=?";
		try{
			PreparedStatement ps=getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				return true;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return false;
	}
	@Override
	public String checkoverTime(int i) {
		if(i==1)
			return Constan.SER_STATUS_TRUE;
		return Constan.SER_STATUS_FALSE;
	}
	@Override
	public Timesheet getTimesheetById(int id) {
		String query = "SELECT * FROM timesheet where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id1 = rs.getInt(1);
				String date = rs.getString(2);
				int duration=rs.getInt(3);
				String description=rs.getString(4);
				String activity=rs.getString(5);
				String overtime=rs.getString(6);
				String location=rs.getString(7);
				int employeeid=rs.getInt(8);
				int projectid=rs.getInt(9);
				Timesheet ts=new Timesheet(id,date,duration,description,activity,overtime,location,employeeid,projectid);
				return ts;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return null;
	}
	@Override
	public ArrayList<Timesheet> getTimesheetByEmployeeid(int id) {
		String query = "SELECT * FROM timesheet where employeeid=?";
		ArrayList<Timesheet> list = new ArrayList<>();
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {	
				int id1 = rs.getInt(1);
				String date = rs.getString(2);
				int duration=rs.getInt(3);
				String description=rs.getString(4);
				String activity=rs.getString(5);
				String overtime=rs.getString(6);
				String location=rs.getString(7);
				int employeeid=rs.getInt(8);
				int projectid=rs.getInt(9);
				Timesheet ts=new Timesheet(id1,date,duration,description,activity,overtime,location,employeeid,projectid);
				list.add(ts);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return list;
	}
	@Override
	public ArrayList<Timesheet> getTimesheetByProjectid(int id) {
		String query = "SELECT * FROM timesheet where projectid=?";
		ArrayList<Timesheet> list = new ArrayList<>();
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id1 = rs.getInt(1);
				String date = rs.getString(2);
				int duration=rs.getInt(3);
				String description=rs.getString(4);
				String activity=rs.getString(5);
				String overtime=rs.getString(6);
				String location=rs.getString(7);
				int employeeid=rs.getInt(8);
				int projectid=rs.getInt(9);
				Timesheet ts=new Timesheet(id1,date,duration,description,activity,overtime,location,employeeid,projectid);
				list.add(ts);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return list;
	}
	@Override
	public int deleteTimesheetbyEmp(int employeeid) {
		String query = "delete from timesheet where employeeid=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, employeeid);
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}
	@Override
	public int deleteTimesheetbyPj(int projectid) {
		// TODO Auto-generated method stub
		String query = "delete from timesheet where projectid=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, projectid);
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}
	@Override
	public String checkLocation(int i) {
		if(i==1) return Constan.LOCATION_THNC;
		else if(i==2) return Constan.LOCATION_HN;
		else return Constan.LOCATION_HCM;
	}
	@Override
	public ArrayList<TimesheetDetail> getTimeSheetDetail() { 
		ArrayList<TimesheetDetail> dsts = new ArrayList<TimesheetDetail>();
		String query = "SELECT employee.name,employee.username from employee inner join timesheet on employee.id=timesheet.employeeid ";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String date = rs.getString(2);
				int duration = rs.getInt(3);
				String description=rs.getString(4);
				String activity=rs.getString(5);
				String overtime=rs.getString(6);
				String location=rs.getString(7);
				int employeeid=rs.getInt(8);
				int projectid=rs.getInt(9);
				String employeeName=rs.getString(10);
				String projectName=rs.getString(11);
				TimesheetDetail ts = new TimesheetDetail(id, date, duration,description,activity,overtime,location,employeeid,projectid,employeeName,projectName );
				dsts.add(ts);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return dsts;
	}
}
